﻿Namespace DB

    Public Enum DBTableType
        Table = 0
        Query = 1
    End Enum

    Public Class DBTable
        Implements System.ComponentModel.INotifyPropertyChanged

        Private _TableName As String = ""
        Private _TableType As DBTableType = DBTableType.Table
        Private WithEvents _FieldList As New DBFieldList(Me)

        Private _BackupTableName As String = ""
        Private _BackupFieldList As New DBFieldList(Me)

        Private _IsModified As Boolean = False
        Private _LoadState As MetisLoadState = MetisLoadState.NewDefinition

        Private _MetisCore As MetisCore

        Public Event PropertyChanged(ByVal sender As Object, ByVal e As System.ComponentModel.PropertyChangedEventArgs) Implements System.ComponentModel.INotifyPropertyChanged.PropertyChanged

        Sub New(ByVal MetisCore As MetisCore, ByVal TableName As String, ByVal TableType As DBTableType)
            Me.New(MetisCore, TableName, TableType, MetisLoadState.NewDefinition)
        End Sub

        Sub New(ByVal MetisCore As Core.MetisCore, ByVal TableName As String, ByVal TableType As DBTableType, ByVal InitState As MetisLoadState)
            _MetisCore = MetisCore
            _TableName = TableName.ToUpper
            _BackupTableName = _TableName
            _TableType = TableType
            _LoadState = InitState
            If _LoadState = MetisLoadState.Loaded Then Reload()
        End Sub

        Sub Reload()
            Dim OraReader As OracleDataReader
            Dim OraCmd As OracleCommand
            _TableName = _BackupTableName
            OraCmd = New OracleCommand(String.Format("SELECT column_id, column_name, data_type, NVL(data_length,0), nullable, data_default " & _
                                                     "FROM user_tab_columns where table_name='{0}' order BY column_id", _TableName), _MetisCore.Connection)

            OraReader = OraCmd.ExecuteReader
            _FieldList.Clear()
            _BackupFieldList.Clear()
            Do While OraReader.Read
                Dim tmpDBField As New DBField()
                tmpDBField.DBfieldID = OraReader.GetInt32(0)
                tmpDBField.DBfieldName = OraReader.GetString(1)
                tmpDBField.DBfieldTypeAsString = OraReader.GetString(2)
                tmpDBField.DBfieldLength = OraReader.GetInt32(3)
                If OraReader.GetString(4) = "Y" Then
                    tmpDBField.DBfieldNullable = True
                Else
                    tmpDBField.DBfieldNullable = False
                End If

                Try
                    tmpDBField.DBfieldDefault = OraReader.GetString(5)
                Catch ex As Exception
                    tmpDBField.DBfieldDefault = ""
                End Try

                _FieldList.Add(tmpDBField)
                _BackupFieldList.Add(tmpDBField.Clone)
            Loop
            OraReader.Close()

            _LoadState = MetisLoadState.Loaded
            _IsModified = False
        End Sub

        Private Function TableExists(ByVal TableName As String) As Boolean
            Dim OraReader As OracleDataReader
            Dim OraCmd As OracleCommand
            OraCmd = New OracleCommand(String.Format("SELECT table_name FROM user_tables WHERE table_name='{0}'", TableName.Trim.ToUpper), _MetisCore.Connection)
            OraReader = OraCmd.ExecuteReader
            Dim tmpResult As Boolean = OraReader.Read
            OraReader.Close()
            Return tmpResult
        End Function

        Private Function CreateFieldTypeSetString(ByVal NewField As DBField, ByVal OldField As DBField) As String
            Dim sFTypeSetString As String = NewField.DBfieldTypeAsString

            'Field Size
            If OldField Is Nothing OrElse (OldField.DBfieldLength <> NewField.DBfieldLength Or _
                                           OldField.DBfieldType <> NewField.DBfieldType) Then
                If (NewField.DBfieldLength > 0) And NewField.DBfieldType <> DBFieldType.DBdate Then
                    sFTypeSetString &= "(" & NewField.DBfieldLength & ")"
                End If
            End If

            'Field Default Value
            If OldField Is Nothing OrElse OldField.DBfieldDefault <> NewField.DBfieldDefault Then
                If Not String.IsNullOrEmpty(NewField.DBfieldDefault) Then
                    If NewField.DBfieldType = DBFieldType.DBnumber Then
                        sFTypeSetString &= " DEFAULT " & NewField.DBfieldDefault
                    Else
                        sFTypeSetString &= " DEFAULT '" & NewField.DBfieldDefault.Replace("'", "''") & "'"
                    End If
                ElseIf OldField IsNot Nothing Then
                    sFTypeSetString &= " DEFAULT NULL"
                End If
            End If

            'Nullable?
            If OldField Is Nothing OrElse OldField.DBfieldNullable <> NewField.DBfieldNullable Then
                If NewField.DBfieldNullable Then
                    sFTypeSetString &= " NULL"
                Else
                    sFTypeSetString &= " NOT NULL"
                End If
            End If

            Return sFTypeSetString
        End Function

        Sub Save()
            Dim sSetString As String = ""
            Dim SQLscript As String = ""

            If Not TableExists(_TableName) Then
                For Each item As DBField In _FieldList
                    sSetString &= String.Format(", {0} {1}", item.DBfieldName, CreateFieldTypeSetString(item, Nothing))
                Next
                SQLscript &= String.Format("EXECUTE IMMEDIATE 'CREATE TABLE {0} ({1})';", _TableName, sSetString.Substring(1)) & vbCrLf
                _BackupTableName = _TableName
                _MetisCore.DBTables.Add(Me)
            Else
                'Remove Fields that are requested to be removed
                If _BackupFieldList.Count > 0 Then
                    For i As Integer = _BackupFieldList.Count - 1 To 0
                        If Not Me._FieldList.ContainsFieldID(_BackupFieldList(i).DBfieldID) Then
                            SQLscript &= String.Format("EXECUTE IMMEDIATE 'ALTER TABLE {0} DROP COLUMN {1}';", _TableName, _BackupFieldList(i).DBfieldName) & vbCrLf
                        End If
                    Next
                End If

                'rename physical db-fields temporary to prevent conflicts (e.g. the user swapped the name of two fields
                For Each item As DBField In _FieldList
                    Dim OldField = _BackupFieldList.GetFieldByID(item.DBfieldID)
                    If (OldField IsNot Nothing) AndAlso (Not item.DBfieldName.Equals(OldField.DBfieldName, StringComparison.OrdinalIgnoreCase)) Then
                        SQLscript &= String.Format("EXECUTE IMMEDIATE 'ALTER TABLE {0} RENAME COLUMN {1} TO F#{2}';", _TableName, OldField.DBfieldName, item.DBfieldID) & vbCrLf
                    End If
                Next

                'rename temporary db-fields to final name and update fielddefinition if something has changed
                For Each item As DBField In _FieldList
                    Dim OldField = _BackupFieldList.GetFieldByID(item.DBfieldID)
                    If OldField IsNot Nothing Then
                        If Not item.DBfieldName.Equals(OldField.DBfieldName, StringComparison.OrdinalIgnoreCase) Then
                            SQLscript &= String.Format("EXECUTE IMMEDIATE 'ALTER TABLE {0} RENAME COLUMN F#{1} TO {2}';", _TableName, item.DBfieldID, item.DBfieldName) & vbCrLf
                        End If
                    End If
                Next

                'alter DB-field definition or add new if it does not exist
                For Each item As DBField In _FieldList
                    Dim OldField = _BackupFieldList.GetFieldByID(item.DBfieldID)
                    Dim sFTypeSetString As String = CreateFieldTypeSetString(item, OldField)

                    If OldField IsNot Nothing Then
                        'Redefine Field-Definition if something changed
                        If item.DBfieldType <> OldField.DBfieldType Or item.DBfieldLength <> OldField.DBfieldLength Or _
                           item.DBfieldDefault <> OldField.DBfieldDefault Or item.DBfieldNullable <> OldField.DBfieldNullable Then

                            SQLscript &= String.Format("EXECUTE IMMEDIATE 'ALTER TABLE {0} MODIFY ({1} {2})';", _TableName, item.DBfieldName, sFTypeSetString) & vbCrLf
                        End If
                    Else 'Add new field if it does not exist
                        SQLscript &= String.Format("EXECUTE IMMEDIATE 'ALTER TABLE {0} ADD ({1} {2})';", _TableName, item.DBfieldName, sFTypeSetString) & vbCrLf
                    End If

                Next
            End If

            If SQLscript <> "" Then
                Dim OraCmd As New OracleCommand(String.Format("BEGIN {0} END;", SQLscript), _MetisCore.Connection)
                OraCmd.ExecuteNonQuery()
            End If

            Reload()


        End Sub

        Public Property TableName() As String
            Get
                Return _TableName
            End Get
            Set(ByVal value As String)
                _TableName = value.ToUpper
                RaiseEvent PropertyChanged(Me, New System.ComponentModel.PropertyChangedEventArgs("TableName"))
            End Set
        End Property

        Public ReadOnly Property TableType() As DBTableType
            Get
                Return _TableType
            End Get
        End Property

        Public ReadOnly Property FieldList() As DBFieldList
            Get
                If _LoadState = MetisLoadState.NotLoaded Then Reload()
                Return _FieldList
            End Get
        End Property

        Public ReadOnly Property IsModified() As Boolean
            Get
                Return _IsModified
            End Get
        End Property

        Public Overrides Function ToString() As String
            Return _TableName
        End Function

        Public Function Clone() As DBTable
            Dim tmpTable As New DBTable(_MetisCore, _TableName, _TableType, _LoadState)
            If _LoadState <> MetisLoadState.Loaded Then
                For Each item As DBField In _FieldList
                    tmpTable._FieldList.Add(item.Clone)
                Next
                For Each item As DBField In _BackupFieldList
                    tmpTable._BackupFieldList.Add(item.Clone)
                Next
            End If
            Return tmpTable
        End Function

    End Class
End Namespace

